package com.utils.db;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.utils.StringUtils;

/**
 * SQL处理工具类
 *
 * @author: dingjiejun
 * @version: 1.0
 * @date: 2017/3/23
 */
public class SqlUtils {

    /**
     * 剔除sql注入风险字符
     *
     * @param sSql
     * @return
     */
    public static String filterInjection(String sSql) {
        if (sSql != null && sSql.length() > 0) {
            sSql = sSql.replaceAll("(/\\*[\\s\\S]*?\\*/)", "");
            sSql = sSql.replaceAll("([;&=\\?\\*]+)", "");
            sSql = sSql.replaceAll("(\\-{2})", "");
        }
        return sSql;
    }

    /**
     * 剔除sql注入风险字符
     *
     * @param sSql
     * @return
     */
    public static String filterInjectionSimply(String sSql) {
        if (sSql != null && sSql.length() > 0) {
            sSql = sSql.replaceAll("(/\\*[\\s\\S]*?\\*/)", "");
            sSql = sSql.replaceAll("([;&\\?\\*]+)", "");
            sSql = sSql.replaceAll("(\\-{2})", "");
        }
        return sSql;
    }

    private static Pattern ORDER_PATTERN = Pattern.compile("order\\s*by[\\s\\S]*", Pattern.CASE_INSENSITIVE);

    /**
     * 去除sql中的order语句
     *
     * @param sql
     * @return
     */
    public static String removeOrders(String sql) {
        if (StringUtils.isEmpty(sql)) {
            return sql;
        }
        Matcher m = ORDER_PATTERN.matcher(sql);

//        while (m.find()) {
//            m.appendReplacement(sb, "");
//        }
//        m.appendTail(sb);
//        return sb.toString();

        //用正则替换特殊情况不好处理，通过遍历解决
        if (m.find()) {
            StringBuffer sb = new StringBuffer();
            String s = sql.substring(m.start(), m.end());
            char[] sA = s.toCharArray();
            //主要匹配为匹配的 )
            int start = 0;
            int l = sA.length;
            int i = 0;
            while (i < l) {
                if (sA[i] == '(') {
                    start++;
                } else if (sA[i] == ')') {
                    start--;
                }
                if (start < 0) {
                    break;
                }
                i++;
            }
            m.appendReplacement(sb, s.substring(i));
            m.appendTail(sb);
            return removeOrders(sb.toString());
        } else {
            return sql;
        }
    }

    /**
     * 根据数据库类型生成分页查询SQL，待补充
     *
     * @param sql
     * @param dbtype   数据库类型
     * @param firstRow 起始记录
     * @param limitRow 限制记录数
     * @return
     */
    public static String buildLimitSql(String sql, DataBaseType dbtype, long firstRow, long limitRow) {

        switch (dbtype) {
            case MYSQL: {
                StringBuilder pagingSelect = new StringBuilder(sql.length() + 100);
                pagingSelect.append("select * from (");
                pagingSelect.append(sql);
                pagingSelect.append(") as qt limit ");
                if (firstRow > 0) {
                    pagingSelect.append(firstRow);
                    pagingSelect.append(",");
                }
                pagingSelect.append(limitRow);
                return pagingSelect.toString();
            }
            case ORACLE: {
                StringBuilder pagingSelect = new StringBuilder(sql.length() + 100);
                if (firstRow > 0) {
                    //pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ").append(sql)
                    //        .append(" ) row_ ) r_ where rownum_ <= ").append(firstRow + limitRow).append(" and rownum_ > ")
                    //        .append(firstRow);

                    pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ").append(sql)
                            .append(" ) row_  where rownum <= ").append(firstRow + limitRow)
                            .append(") r_ where rownum_ > ")
                            .append(firstRow);
                } else {
                    pagingSelect.append("select * from ( ").append(sql).append(" ) r_ where rownum <= ").append(limitRow);
                }
                return pagingSelect.toString();
            }
            //TODO:其他数据库
            default:
                throw new DatabaseException("该类型数据库暂不支持");
        }

    }

    private final static String MARK = "?";
    private final static String SEP = ",";

    @SuppressWarnings("unchecked")
    public static SqlArgument getSqlArgument(String sql, Object[] params) {
        SqlArgument sqlArgument = new SqlArgument();
        if(params.length==1 && params[0] instanceof Map){
            sqlArgument.setText(sql);
            sqlArgument.setNamedQuery(true);
            sqlArgument.setParamMap((Map<String, Object>)params[0]);
            return sqlArgument;
        }
        //检查下是否有Collection参数，将其展开
        boolean hasMultiValue = false;
        for (Object param : params) {
            if (param == null) {
                continue;
            }
            if (param instanceof Collection) {
                hasMultiValue = true;
                break;
            } else if (param.getClass().isArray()) {
                hasMultiValue = true;
                break;
            }
        }

        if (hasMultiValue) {
            String[] sqlSections = StringUtils.splitPreserveAllTokens(sql, MARK);
            StringBuilder sb = new StringBuilder(sql.length());
            List<Object> args = new ArrayList<Object>();
            sb.append(sqlSections[0]);
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                String seq = MARK;
                if (param != null) {
                    if (param instanceof Collection) {
                        Collection collection = (Collection) param;
                        args.addAll(collection);
                        seq = StringUtils.repeat(MARK, SEP, collection.size());
                    } else if (param.getClass().isArray()) {
                        Object[] arr = (Object[]) param;
                        args.addAll(Arrays.asList(arr));
                        seq = StringUtils.repeat(MARK, SEP, args.size());
                    } else {
                        args.add(param);
                    }
                } else {
                    args.add(null);
                }
                sb.append(seq).append(sqlSections[i + 1]);
            }
            sqlArgument.setText(sb.toString());
            sqlArgument.setArgs(args.toArray());
        } else {
            if (sql.indexOf(MARK) == sql.lastIndexOf(MARK) && params.length > 1) {
                //视作唯一的数组参数
                sqlArgument.setText(StringUtils.replace(sql, MARK, StringUtils.repeat(MARK, SEP, params.length)));
                sqlArgument.setArgs(params);
            } else {
                sqlArgument.setText(sql);
                sqlArgument.setArgs(params);
            }
        }
        return sqlArgument;
    }

}

